%TYPE and %ROWTYPE seem like an easy way to make sure that a variable’s type always matches the type of the relevant
column in an existing table. If the column type changes, then the variable changes with it.
However, Oracle Forms compiled against a procedure using either of these two symbols won’t get the benefit of that flexibility. Instead, at compile
time, the relevant type is looked up from the underlying database and used in the form. If the column type changes later or the form is running
against a database with different length semantics, attempting to use the form results in an "ORA-04062: Signature of package has been changed" error
on the package in question. And the form needs to be recompiled on exactly the same database environment where it will run to avoid the error.
Note that %TYPE and %ROWTYPE can be used in a package’s private procedures and functions, private package variables, and
local variables without issue, but not in the package specification.
Noncompliant code example
CREATE OR REPLACE PACKAGE PACK IS
TYPE mytype IS RECORD (
var1 mytable.mycolumn%TYPE -- Noncompliant
);
FUNCTION MY_FUNC(param1 IN mytable.mycolumn%TYPE) RETURN VARCHAR2; -- Noncompliant
FUNCTION MY_FUNC2(param1 IN mytable%ROWTYPE) RETURN VARCHAR2; -- Noncompliant
END;
Compliant solution
CREATE OR REPLACE PACKAGE PACK IS
TYPE mytype IS RECORD (
var1 VARCHAR2(100) -- Compliant
);
FUNCTION MY_FUNC(param1 IN VARCHAR2) RETURN VARCHAR2; -- Compliant
TYPE myrowtype IS RECORD (
col1 NUMBER,
col2 VARCHAR2(30)
);
FUNCTION MY_FUNC2(param1 IN myrowtype) RETURN VARCHAR2; -- Compliant
END;
